REM  SQL scripts for Chapter 15 of ORACLE8 DBA Handbook by Kevin Loney.
REM  See www.osborne.com for updates to these scripts.

create public database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'hq';


create public database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'hq';


hq =(DESCRIPTION=
      (ADDRESS=
            (PROTOCOL=TCP)
            (HOST=HQ)
            (PORT=1521))
      (CONNECT DATA=
            (SID=loc))))

create public database link HR_LINK
using 'hq';

create public database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'hq';


create public database link HR_LINK
using 'hq';

create view REMOTE_EMP
as select * from EMPLOYEE@HR_LINK
where Ename=User;


drop database link HR_LINK;

create public database link HR_LINK
connect to HR identified by NEWPASSWORD
using 'hq';

create database link SCOTT.HR_LINK
connect to HR identified by PUFFINSTUFF
using 'hq';

create public database link TRIGGER_LINK
using 'remote1';

create trigger COPY_DATA
after insert on EMPLOYEE
for each row
begin
     insert into EMPLOYEE@TRIGGER_LINK
     values
     (:new.Empno, :new.Ename, :new.Deptno,
     :new.Salary, :new.Birth_Date, :new.Soc_Sec_Num);
end;
/

select Trigger_Type,
       Triggering_Event,
       Table_Name
  from DBA_TRIGGERS
 where Trigger_Name = 'COPY_DATA';

select Trigger_Body
  from DBA_TRIGGERS
 where Trigger_Name = 'COPY_DATA';

create database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'hq';

create snapshot EMP_DEPT_COUNT
pctfree 5
tablespace SNAP
storage (initial 100K next 100K pctincrease 0)
refresh complete
      start with SYSDATE
      next SYSDATE+7
as select Deptno, COUNT(*) Dept_Count
     from EMPLOYEE@HR_LINK
    group by Deptno;

drop snapshot EMP_DEPT_COUNT;
alter snapshot EMP_DEPT_COUNT pctfree 5;

select
 Name,           /*Name of the view used for the snapshot*/
 Last_Refresh,   /*Timestamp for the last refresh*/
 Type,           /*Type of refresh used for automatic refreshes*/
 Query           /*Query used to create the snapshot*/
from DBA_SNAPSHOTS;



DBMS_REFRESH.MAKE
( name      IN VARCHAR2,
  list      IN VARCHAR2,
  next_date IN DATE,
  interval  IN VARCHAR2,
  implicit_destroy     IN BOOLEAN DEFAULT FALSE,
  lax                  IN BOOLEAN DEFAULT FALSE,
  job                  IN BINARY_INTEGER DEFAULT 0,
  rollback_seg         IN VARCHAR2 DEFAULT NULL,
  push_deferred_rpc    IN BOOLEAN DEFAULT TRUE,
  refresh_after_errors IN BOOLEAN DEFAULT FALSE );

execute DBMS_REFRESH.MAKE
(name => 'emp_group',
 list => 'local_emp, local_dept',
 next_date => SysDate,
 interval => SysDate+7)

DBMS_REFRESH.ADD
( name      IN VARCHAR2,
  list      IN VARCHAR2,
  lax       IN BOOLEAN  DEFAULT FALSE );

DBMS_REFRESH.SUBTRACT
( name      IN VARCHAR2,
  list      IN VARCHAR2,
  lax       IN BOOLEAN  DEFAULT FALSE );

DBMS_REFRESH.CHANGE
( name                 IN VARCHAR2,
  next_date            IN DATE DEFAULT NULL,
  interval             IN VARCHAR2 DEFAULT NULL,
  implicit_destroy     IN BOOLEAN  DEFAULT NULL,
  rollback_seg         IN VARCHAR2 DEFAULT NULL,
  push_deferred_rpc    IN BOOLEAN DEFAULT NULL,
  refresh_after_errors IN BOOLEAN DEFAULT NULL);

execute DBMS_REFRESH.CHANGE
(name => 'emp_group',
 next_date => null,
 interval => SysDate+3);

execute DBMS_REFRESH.DESTROY(name => 'emp_group');

execute DBMS_SNAPSHOT.REFRESH('emp_dept_count','?');

execute DBMS_SNAPSHOT.REFRESH_ALL;

execute DBMS_REFRESH.REFRESH('emp_group');


create snapshot log on EMPLOYEE
tablespace DATA_2
storage(initial 100K next 50K pctincrease 0)
pctfree 5 pctused 90;

alter snapshot log EMPLOYEE
pctfree 10;

drop snapshot log on EMPLOYEE;


create database link HR_LINK
connect to HR identified by PUFFINSTUFF
using 'hq';

create snapshot EMP_DEPT_COUNT
refresh complete
      start with SYSDATE
      next SYSDATE+7
as select Deptno, COUNT(*) Dept_Count
     from EMPLOYEE@HR_LINK
    group by Deptno;


execute DBMS_SNAPSHOT.PURGE_LOG
(master => 'EMPLOYEE',
    num => 1,
   flag => 'DELETE');

truncate table EMPLOYEE preserve snapshot log;

truncate table EMPLOYEE purge snapshot log;

CREATE PUBLIC DATABASE LINK loc.hq.mycorp.com
USING 'connect string';

PROCEDURE SUBMIT
  ( job       OUT BINARY_INTEGER,
    what      IN  VARCHAR2,
    next_date IN  DATE DEFAULT sysdate,
    interval  IN  VARCHAR2 DEFAULT 'null',
    no_parse  IN  BOOLEAN DEFAULT FALSE);


variable jobno number;
begin
  DBMS_JOB.SUBMIT(:jobno,'myproc',SysDate,SysDate+1);
  commit;
end;
/

print jobno


PROCEDURE BROKEN
( job       IN  BINARY_INTEGER,
  broken    IN  BOOLEAN,
  next_date IN  DATE DEFAULT SYSDATE );

PROCEDURE CHANGE
( job       IN  BINARY_INTEGER,
  what      IN  VARCHAR2,
  next_date IN  DATE,
  interval  IN  VARCHAR2);




